package Database;

import sun.reflect.generics.reflectiveObjects.NotImplementedException;
import vo.Program;

import java.sql.*;
import java.util.ArrayList;
import java.util.UUID;

public class DatabaseOperator
{
	private static final String DEFAULT_USERNAME = "whu_iss_2017" /*"root"*/;
	private static final String DEFAULT_PASSWORD = "iss_java_2017" /*"980629"*/;
	private static final String DEFAULT_DBURL  = "jdbc:mysql://119.27.166.115:2017/java_exp"/*"jdbc:mysql://localhost:3306/mysql"*/;
	private static final String DEFAULT_TABLE_NAME = "program" /*"programList"*/;
	private Connection connection;
	private Statement statement;

	public DatabaseOperator()
	{
		this(DEFAULT_DBURL, DEFAULT_USERNAME, DEFAULT_PASSWORD);
	}

	public DatabaseOperator(String dbUrl, String userName, String password)
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(dbUrl, userName, password);
			statement = connection.createStatement();
		}
		catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void changeLength(int newLength, String columnName, String tableName)
	{
		try
		{
			String format = String.format("ALTER TABLE +" + tableName + "+ MODIFY COLUMN %s VARCHAR(%d);", columnName, newLength);
			statement.executeUpdate(format);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void changeLength(int newLength, String column)
	{
		changeLength(newLength, column, DEFAULT_TABLE_NAME);
	}

	public void changeConnection(String newDbUrl, String userName, String password)
	{
		try
		{
			connection.close();
			connection = DriverManager.getConnection(newDbUrl, userName, password);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void changeConnection(String newDbUrl)
	{
		try
		{
			connection.close();
			connection = DriverManager.getConnection(newDbUrl);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void insert(Program program)
	{
		if(program != null)
		{
			insert(program.getId(),
				   program.getCountry(),
				   program.getUniversity(),
				   program.getSchool(),
				   program.getProgramName(),
				   program.getHomepage(),
				   program.getLocation(),
				   program.getEmail(),
				   program.getPhoneNumber(),
				   program.getDegree(),
				   program.getDeadlineWithAid(),
				   program.getDeadlineWithoutAid());
		}
	}

	public void deleteTable()
	{

	}
	public void insert(String id,
					   String country,
					   String university,
					   String school,
					   String program_name,
					   String homepage,
					   String location,
					   String email,
					   String phone_number,
					   String degree,
					   String deadlineWithAid,
					   String deadlineWithoutAid)
	{
		insert(id, country, university, school, program_name, homepage, location, email, phone_number, degree, deadlineWithAid, deadlineWithoutAid, DEFAULT_TABLE_NAME);
	}
	public void insert(String id,
					   String country,
					   String university,
					   String school,
					   String program_name,
					   String homepage,
					   String location,
					   String email,
					   String phone_number,
					   String degree,
					   String deadlineWithAid,
					   String deadlineWithoutAid, String tableName)
	{
		try
		{
			String replacement = "`";
			school = school.replace("'",
									replacement);
			program_name = program_name.replace("'",
												replacement);
			homepage = homepage.replace("'",
										replacement);
			location = location.replace("'",
										replacement);
			email = email.replace("'",
								  replacement);
			deadlineWithoutAid = deadlineWithoutAid.replace("'",
															replacement);

			deadlineWithoutAid = deadlineWithoutAid.replace("n'", " not");
			while (deadlineWithoutAid.length() > 255)
			{
				deadlineWithoutAid = deadlineWithoutAid.replaceFirst(".*?\\.", "").trim();
			}
			String format = String.format("INSERT INTO " + tableName + " VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', " +
												  "'%s', '%s', '%s', '%s', '%s');", id, country, university, school, program_name, homepage,
										  location, email, phone_number, degree, deadlineWithAid, deadlineWithoutAid);

			statement.executeUpdate(format);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void insert(String school, String programName, String homePage, String location, String email, String degree, String phoneNumber, String deadlineWithoutAid)
	{
		insert(UUID.randomUUID().toString().replace("-", ""), "United Kingdom", "University of Liverpool", school, programName, homePage, location, email, degree, phoneNumber, null, deadlineWithoutAid);
	}

	public Program[] getTopById(int lastIndex, String property, String pattern)
	{
		return getTopById(0, lastIndex, property, pattern);
	}
	public Program[] getTopById(int beforeIndex, int lastIndex, String property, String pattern)
	{
		return getTopById(beforeIndex, lastIndex, DEFAULT_TABLE_NAME, property, pattern);
	}
	public Program[] getTopById(int beforeIndex, int lastIndex, String tableName, String property, String pattern)
	{
		ResultSet resultSet = null;
		try
		{
			Program[] programs;
			resultSet = statement.executeQuery("SELECT DISTINCT * FROM "+ tableName + " WHERE " + property + " REGEXP '" + pattern + "' ORDER BY id LIMIT "+ beforeIndex + ", " + lastIndex+";");
			programs = resultSetToPrograms(resultSet);
			return programs;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}
	public int count(String tableName)
	{
		try
		{
			ResultSet resultSet = statement.executeQuery("SELECT count(*) FROM " + tableName);
			if(resultSet.next())
			{
				int anInt = resultSet.getInt(1);
				return anInt;
			}
			return 0;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return 0;
	}
	public int count()
	{
		return count(DEFAULT_TABLE_NAME);
	}

	public String[] get(String column)
	{
		return get(column, DEFAULT_TABLE_NAME);
	}

	public String[] get(String column, String tableName)
	{
		ArrayList<String> stringArrayList = new ArrayList<>();
		ResultSet resultSet = null;
		try
		{
			resultSet = statement.executeQuery("SELECT DISTINCT " + column + " FROM " + tableName);
			while(resultSet.next())
			{
				stringArrayList.add(resultSet.getString(column));
			}
			String[] strings = new String[stringArrayList.size()];
			stringArrayList.toArray(strings);
			return strings;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}

	public Program[] search(String phrase, String value)
	{
		return search(phrase, value, DEFAULT_TABLE_NAME);
	}
	public Program[] search(String phrase, String value, String tableName)
	{
		ResultSet resultSet = null;
		try
		{
			resultSet = statement.executeQuery("SELECT DISTINCT * from "+ tableName +" WHERE " + phrase + " = " + value);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return resultSetToPrograms(resultSet);
	}

	public Program[] getAll(String tableName)
	{
		ArrayList<Program> programs = new ArrayList<>();
		try
		{
			ResultSet resultSet = statement.executeQuery("SELECT DISTINCT * FROM " + tableName);

			return resultSetToPrograms(resultSet);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}
	public Program[] getAll()
	{
		return getAll(DEFAULT_TABLE_NAME);
	}

	public void removeAll(String tableName)
	{
		try
		{
			statement.executeUpdate("DELETE FROM " + tableName);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	public void removeAll()
	{
		removeAll(DEFAULT_TABLE_NAME);
	}

	private Program[] resultSetToPrograms(ResultSet resultSet)
	{
		if (resultSet == null)
		{
			return null;
		}
		ArrayList<Program> programs = new ArrayList<>();
		Program[] programs1 = null;
		try
		{
			while (resultSet.next())
			{

				programs.add(new Program(resultSet.getString("Id"),
						resultSet.getString("country"),
						resultSet.getString("university"),
						resultSet.getString("school"),
						resultSet.getString("program_name"),
						resultSet.getString("homepage"),
						resultSet.getString("location"),
						resultSet.getString("email"),
						resultSet.getString("phone_number"),
						resultSet.getString("degree"),
						resultSet.getString("deadline_with_aid"),
						resultSet.getString("deadline_without_aid")));

			}
			programs1 = new Program[programs.size()];
			programs.toArray(programs1);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return programs1;
	}

}

